# _*_ coding:utf-8 _*_
# @File  : work.py
# @Time  : 2021-03-16 15:16
# @Author: zizle

import MySQLdb
from .pool import fa_pool
from logger import logger


class FAConnection(object):
    def __init__(self, conn_name=''):
        self.conn_name = conn_name
        self.conn = fa_pool.connection()  # 从连接池取得一个连接
        self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)  # 连接的游标
        self.cursor.self_conn = None

    def correct_table_auto_increment(self, table_name, finish_closed=False):
        # 重置数据表的auto_increment的值.(强迫症才做,此步也可不用)，好像也可直接设置为1,会默认以最大值开始(但可能性能比较差)
        max_id_obj = self.query(f'SELECT MAX(id) AS mId FROM {table_name};', keep_conn=True)
        number = max_id_obj[0]['mId'] if max_id_obj else 0
        max_id = number + 1 if number else 1
        self.execute(f'ALTER TABLE {table_name} AUTO_INCREMENT=%s;', param=[max_id], keep_conn=True)
        if finish_closed:
            self.close()

    def begin(self):
        self.conn.begin()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

    def __enter__(self):
        self.begin()
        self.cursor.self_conn = self
        return self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type:
            self.rollback()
            print(f'MySQL Error:{ exc_type.__name__ }:{ exc_val }')
        else:
            self.commit()
        self.close()

    def execute(self, sql, param=None, many=False, keep_conn=False):
        self.begin()
        is_success = True
        count = 0
        if param is None:
            param = []
        try:
            if many:
                count = self.cursor.executemany(sql, param)
            else:
                count = self.cursor.execute(sql, param)
        except MySQLdb.Error as e:
            self.rollback()
            is_success = False
            logger.error('业务【{}】操作数据库失败-{}:{}'.format(self.conn_name, e.args[0], e.args[1]))
        else:
            self.commit()
        finally:
            if not keep_conn:
                self.close()
            return count, is_success

    def execute_tasks(self, sql_list, param_list, keep_conn=False, many=False):  # 事务方式执行
        self.begin()
        is_success = True
        try:
            if many:
                for sql_tuple in zip(sql_list, param_list):
                    # print(sql_tuple[0])
                    # print(sql_tuple[1])
                    if sql_tuple[1] is None:
                        r = self.cursor.executemany(sql_tuple[0])
                    else:
                        r = self.cursor.executemany(sql_tuple[0], sql_tuple[1])
                    # print('执行结果', r)
            else:
                for sql_tuple in zip(sql_list, param_list):
                    # print(sql_tuple)
                    if sql_tuple[1] is None:
                        self.cursor.execute(sql_tuple[0])
                    else:
                        self.cursor.execute(sql_tuple[0], sql_tuple[1])
        except MySQLdb.Error as e:
            # print(e)
            self.rollback()
            logger.error('事务型操作数据库失败{}:{}'.format(e.args[0], e.args[1]))
            is_success = False
        else:
            self.commit()
            # print('提交commit')
        finally:
            if not keep_conn:
                self.close()
            return is_success

    def insert(self, sql, param=None, many=False, keep_conn=False):
        self.begin()
        insert_id = 0
        count = 0
        if param is None:
            param = []
        try:
            if many:
                count = self.cursor.executemany(sql, param)
            else:
                count = self.cursor.execute(sql, param)
            self.cursor.execute('SELECT LAST_INSERT_ID() AS insertID;')
            insert_id = self.cursor.fetchone()['insertID']
        except MySQLdb.Error as e:
            print(e)
            self.rollback()
            logger.error('添加数据记录失败-{}:{}'.format(e.args[0], e.args[1]))
        else:
            self.commit()
        finally:
            if not keep_conn:
                self.close()
            return count, insert_id

    def query(self, sql, param=None, fetchone=False, keep_conn=False) -> list:
        records = []
        try:
            if param is None:  # 无参数
                self.cursor.execute(sql)
            else:
                self.cursor.execute(sql, param)
            if fetchone:
                record_obj = self.cursor.fetchone()
                if record_obj:
                    records.append(record_obj)
                else:
                    records.append({})
            else:
                records = list(self.cursor.fetchall())
        except MySQLdb.Error as e:
            print(e)
            print(sql, param)
            logger.error('业务:{} - 查询数据失败-{}:{}'.format(self.conn_name, e.args[0], e.args[1]))

        finally:
            if not keep_conn:
                self.close()
            return records

    def query_paginator(self, sql, param: list = None, page: int = 1, page_size: int = 10, keep_conn=False) -> dict:
        sql = sql.replace('SELECT ', 'SELECT SQL_CALC_FOUND_ROWS ', 1)  # 替换第一个
        sql = sql.replace(';', ' LIMIT %s,%s;')
        param.append((page - 1) * page_size)
        param.append(page_size)
        ret_obj = {'data': [], 'total_count': 0, 'total_page': 1, 'page': 1}
        try:
            self.cursor.execute(sql) if param is None else self.cursor.execute(sql, param)
            ret_obj['data'] = self.cursor.fetchall()
            # 查询一下总数
            total_obj = self.query('SELECT FOUND_ROWS() AS total;', fetchone=True, keep_conn=keep_conn)[0]
            total_count = total_obj['total'] if total_obj else 0  # 当前总数
            ret_obj['total_count'] = total_count
            ret_obj['page'] = page
            ret_obj['page_size'] = page_size
            ret_obj['total_page'] = (total_count + page_size - 1) // page_size
        except MySQLdb.Error as e:
            logger.error(f'分页查询数据库失败:{e}\nSQL: {sql}')
        finally:
            if not keep_conn:
                self.close()
            return ret_obj

    # 分页查询（预废弃，旧接口可能有在用）
    def paginator_query(self, sql, page, page_size, param=None, keep_conn=False) -> dict:
        ret_obj = {'records': [], 'total_count': 1, 'total_page': 1, 'cur_page': 1}
        try:
            if param is None:
                self.cursor.execute(sql)
            else:
                self.cursor.execute(sql, param)
            ret_obj['records'] = self.cursor.fetchall()
            # 查询一下页码
            total_obj = self.query('SELECT FOUND_ROWS() AS total;', fetchone=True)[0]
            total_count = total_obj['total'] if total_obj else 1  # 当前总页码
            ret_obj['total_count'] = total_count
            ret_obj['total_page'] = (total_count + page_size - 1) // page_size
            ret_obj['cur_page'] = page
        except MySQLdb.Error as e:
            logger.error('业务:{} - 分页查询数据失败-{}:{}'.format(self.conn_name, e.args[0], e.args[1]))
        finally:
            if not keep_conn:
                self.close()
            return ret_obj
